CREATE OR REPLACE EDITIONABLE PACKAGE BODY "SCOTT"."PKG_ALGORITHM" is

  /*------------------------------------------------ calculate the columns of table board_index ----------------------------------------------*/
  procedure markov(p_stock_number in number) is
  begin
    DECLARE
      -- 股票代码
      v_code varchar(100);
      -- 时间区间的开始时间、结束时间
      v_begin_date date;
      v_end_date   date;
      -- 上涨、持平、下跌的总数
      v_total_up     number;
      v_total_middle number;
      v_total_down   number;
      -- 上涨、持平、下跌的次数
      v_up     number;
      v_middle number;
      v_down   number;
      -- 上一条记录的up_down属性
      v_last_up_down number := null;
      -- 上涨、持平、下跌的比率
      v_up_to_up_percentage         number;
      v_up_to_middle_percentage     number;
      v_up_to_down_percentage       number;
      v_middle_to_up_percentage     number;
      v_middle_to_middle_percentage number;
      v_middle_to_down_percentage   number;
      v_down_to_up_percentage       number;
      v_down_to_middle_percentage   number;
      v_down_to_down_percentage     number;
      -- 从上涨到上涨的数量
      v_up_to_up_number number;
      -- 从上涨到持平的数量
      v_up_to_middle_number number;
      -- 从上涨到下跌的数量
      v_up_to_down_number number;
      -- 从持平到上涨的数量
      v_middle_to_up_number number;
      -- 从持平到持平的数量
      v_middle_to_middle_number number;
      -- 从持平到下跌的数量
      v_middle_to_down_number number;
      -- 从下跌到上涨的数量
      v_down_to_up_number number;
      -- 从下跌到持平的数量
      v_down_to_middle_number number;
      -- 从下跌到下跌的数量
      v_down_to_down_number number;
      -- 记录迭代的次数
      v_iterator number;
      -- 初始状态向量
      v_one_vector   number;
      v_two_vector   number;
      v_three_vector number;
      -- 下一个初始状态向量
      v_next_one_vector   number;
      v_next_two_vector   number;
      v_next_three_vector number;
      -- 按照股票代码分组
      CURSOR cur_group_by_stock_code IS
        SELECT t.CODE_ stock_code, count(*) count
          FROM stock_transaction_data_all t
         GROUP BY t.CODE_;
      -- 每只股票的时间区间的迭代列表
      cursor cur_for_iterator is
        select *
          from stock_transaction_data_all t
         where t.date_ >= v_end_date
           and t.code_ = v_code
         order by t.date_ asc;
      -- 每只股票的时间区间的记录
      cursor cur_stda_range is
        select *
          from (select *
                  from (select distinct t.date_, t.code_, msa.UP_DOWN
                          from stock_transaction_data_all t
                          join MDL_STOCK_ANALYSIS msa
                            on msa.CODE_ = t.CODE_
                           and msa.DATE_ = t.DATE_
                           and msa.UP_DOWN is not null
                           and t.date_ >= v_end_date
                         where t.code_ = v_code
                         order by t.date_ asc)
                 where rownum <= p_stock_number) t1;
    BEGIN
      FOR i IN cur_group_by_stock_code LOOP
        IF i.count >= p_stock_number * 2 THEN
          -- 初始化
          v_total_up                    := 0;
          v_total_middle                := 0;
          v_total_down                  := 0;
          v_up                          := 0;
          v_middle                      := 0;
          v_down                        := 0;
          v_last_up_down                := null;
          v_up_to_up_number             := 0;
          v_up_to_middle_number         := 0;
          v_up_to_down_number           := 0;
          v_middle_to_up_number         := 0;
          v_middle_to_middle_number     := 0;
          v_middle_to_down_number       := 0;
          v_down_to_up_number           := 0;
          v_down_to_middle_number       := 0;
          v_down_to_down_number         := 0;
          v_up_to_up_percentage         := 0;
          v_up_to_middle_percentage     := 0;
          v_up_to_down_percentage       := 0;
          v_middle_to_up_percentage     := 0;
          v_middle_to_middle_percentage := 0;
          v_middle_to_down_percentage   := 0;
          v_down_to_up_percentage       := 0;
          v_down_to_middle_percentage   := 0;
          v_down_to_down_percentage     := 0;
          v_one_vector                  := 1;
          v_two_vector                  := 0;
          v_three_vector                := 0;

          v_code := i.stock_code;

          -- 计算每只股票的第一个时间区间的开始时间、结束时间
          select min(t1.date_), max(t1.date_)
            into v_begin_date, v_end_date
            from (select *
                    from (select distinct t.date_
                            from stock_transaction_data_all t
                           where t.code_ = v_code
                           order by t.date_ asc)
                   where rownum <= p_stock_number) t1;

          -- 计算每只股票的时间区间的迭代列表
          for j in cur_for_iterator loop
            v_end_date := j.date_;

            -- 初始化
            v_iterator := 0;

            -- 计算每只股票的时间区间的记录
            for x in cur_stda_range loop

              v_iterator := v_iterator + 1;

              -- 计算连续上涨、持平、下跌的次数
              if v_last_up_down is null then
                v_last_up_down := x.up_down;
              else
                if v_last_up_down = 1 and x.up_down = 1 and
                   v_last_up_down = x.up_down then
                  v_up_to_up_number := v_up_to_up_number + 1;
                end if;
                if v_last_up_down = 1 and x.up_down = 0 and
                   v_last_up_down != x.up_down then
                  v_up_to_middle_number := v_up_to_middle_number + 1;
                end if;
                if v_last_up_down = 1 and x.up_down = -1 and
                   v_last_up_down != x.up_down then
                  v_up_to_down_number := v_up_to_down_number + 1;
                end if;
                if v_last_up_down = 0 and x.up_down = 1 and
                   v_last_up_down != x.up_down then
                  v_middle_to_up_number := v_middle_to_up_number + 1;
                end if;
                if v_last_up_down = 0 and x.up_down = 0 and
                   v_last_up_down = x.up_down then
                  v_middle_to_middle_number := v_middle_to_middle_number + 1;
                end if;
                if v_last_up_down = 0 and x.up_down = -1 and
                   v_last_up_down != x.up_down then
                  v_middle_to_down_number := v_middle_to_down_number + 1;
                end if;
                if v_last_up_down = -1 and x.up_down = 1 and
                   v_last_up_down != x.up_down then
                  v_down_to_up_number := v_down_to_up_number + 1;
                end if;
                if v_last_up_down = -1 and x.up_down = 0 and
                   v_last_up_down != x.up_down then
                  v_down_to_middle_number := v_down_to_middle_number + 1;
                end if;
                if v_last_up_down = -1 and x.up_down = -1 and
                   v_last_up_down = x.up_down then
                  v_down_to_down_number := v_down_to_down_number + 1;
                end if;
              end if;

              -- 计算上涨、持平、下跌的总次数
              /*if x.up_down = 1 then
                v_total_up := v_total_up + 1;
              end if;
              if x.up_down = 0 then
                v_total_middle := v_total_middle + 1;
              end if;
              if x.up_down = -1 then
                v_total_down := v_total_down + 1;
              end if;*/

              -- 表示上一条记录
              v_last_up_down := x.up_down;

            -- 最后那个不算
            /*if v_iterator = p_stock_number then
                                                                                                    if x.up_down = 1 then
                                                                                                      v_total_up := v_total_up - 1;
                                                                                                    end if;
                                                                                                    if x.up_down = 0 then
                                                                                                      v_total_middle := v_total_middle - 1;
                                                                                                    end if;
                                                                                                    if x.up_down = -1 then
                                                                                                      v_total_down := v_total_down - 1;
                                                                                                    end if;
                                                                                                  end if;*/
            end loop;

            -- 计算上涨、持平、下跌的比率
            if v_up_to_up_number + v_up_to_middle_number +
               v_up_to_down_number != 0 then
              v_up_to_up_percentage     := v_up_to_up_number /
                                           (v_up_to_up_number +
                                           v_up_to_middle_number +
                                           v_up_to_down_number);
              v_up_to_middle_percentage := v_up_to_middle_number /
                                           (v_up_to_up_number +
                                           v_up_to_middle_number +
                                           v_up_to_down_number);
              v_up_to_down_percentage   := v_up_to_down_number /
                                           (v_up_to_up_number +
                                           v_up_to_middle_number +
                                           v_up_to_down_number);
            end if;
            if v_middle_to_up_number + v_middle_to_middle_number +
               v_middle_to_down_number != 0 then
              v_middle_to_up_percentage     := v_middle_to_up_number /
                                               (v_middle_to_up_number +
                                               v_middle_to_middle_number +
                                               v_middle_to_down_number);
              v_middle_to_middle_percentage := v_middle_to_middle_number /
                                               (v_middle_to_up_number +
                                               v_middle_to_middle_number +
                                               v_middle_to_down_number);
              v_middle_to_down_percentage   := v_middle_to_down_number /
                                               (v_middle_to_up_number +
                                               v_middle_to_middle_number +
                                               v_middle_to_down_number);
            end if;
            if v_down_to_up_number + v_down_to_middle_number +
               v_down_to_down_number != 0 then
              v_down_to_up_percentage     := v_down_to_up_number /
                                             (v_down_to_up_number +
                                             v_down_to_middle_number +
                                             v_down_to_down_number);
              v_down_to_middle_percentage := v_down_to_middle_number /
                                             (v_down_to_up_number +
                                             v_down_to_middle_number +
                                             v_down_to_down_number);
              v_down_to_down_percentage   := v_down_to_down_number /
                                             (v_down_to_up_number +
                                             v_down_to_middle_number +
                                             v_down_to_down_number);
            end if;

            -- 计算下一个状态向量
            v_next_one_vector   := v_up_to_up_percentage * v_one_vector +
                                   v_middle_to_up_percentage * v_two_vector +
                                   v_down_to_up_percentage * v_three_vector;
            v_next_two_vector   := v_up_to_middle_percentage * v_one_vector +
                                   v_middle_to_middle_percentage *
                                   v_two_vector + v_down_to_middle_percentage *
                                   v_three_vector;
            v_next_three_vector := v_up_to_down_percentage * v_one_vector +
                                   v_middle_to_down_percentage *
                                   v_two_vector + v_down_to_down_percentage *
                                   v_three_vector;

            dbms_output.put_line(v_end_date);
            dbms_output.put_line(v_up_to_up_percentage);
            dbms_output.put_line(v_up_to_middle_percentage);
            dbms_output.put_line(v_up_to_down_percentage);
            dbms_output.put_line(v_middle_to_up_percentage);
            dbms_output.put_line(v_middle_to_middle_percentage);
            dbms_output.put_line(v_middle_to_down_percentage);
            dbms_output.put_line(v_down_to_up_percentage);
            dbms_output.put_line(v_down_to_middle_percentage);
            dbms_output.put_line(v_down_to_down_percentage);
            dbms_output.put_line(v_next_one_vector);
            dbms_output.put_line(v_next_two_vector);
            dbms_output.put_line(v_next_three_vector);
            dbms_output.put_line('------------------------------------------');

            v_one_vector   := v_next_one_vector;
            v_two_vector   := v_next_two_vector;
            v_three_vector := v_next_three_vector;

            -- 初始化
            v_total_up                    := 0;
            v_total_middle                := 0;
            v_total_down                  := 0;
            v_up_to_up_number             := 0;
            v_up_to_middle_number         := 0;
            v_up_to_down_number           := 0;
            v_middle_to_up_number         := 0;
            v_middle_to_middle_number     := 0;
            v_middle_to_down_number       := 0;
            v_down_to_up_number           := 0;
            v_down_to_middle_number       := 0;
            v_down_to_down_number         := 0;
            v_up_to_up_percentage         := 0;
            v_up_to_middle_percentage     := 0;
            v_up_to_down_percentage       := 0;
            v_middle_to_up_percentage     := 0;
            v_middle_to_middle_percentage := 0;
            v_middle_to_down_percentage   := 0;
            v_down_to_up_percentage       := 0;
            v_down_to_middle_percentage   := 0;
            v_down_to_down_percentage     := 0;
          end loop;
        else
          SCOTT.pkg_tool.DEBUG('股票' || i.stock_code || '的交易次数不足' ||
                               p_stock_number * 2 || '，因此忽略');
        END IF;
      END LOOP;
    END;
  END markov;

END PKG_ALGORITHM;